package com.dushibao.dao;

import cn.hutool.core.util.ObjectUtil;
import com.dushibao.common.Constants;
import com.dushibao.entity.Accessory;
import com.dushibao.entity.Goods;

import com.dushibao.entity.GoodsPhoto;
import com.dushibao.utils.DBUtils;
import com.dushibao.utils.Page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class GoodsDao {


    public Integer save(Goods goods) {

        try{
            String sql = "insert into dushibao_goods(name,price,stock,details) values(?,?,?,?)";

            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            stmt.setObject(1,goods.getName());
            stmt.setObject(2,goods.getPrice());
            stmt.setObject(3,goods.getStock());
            stmt.setObject(4,goods.getDetails());

            stmt.executeUpdate();

            ResultSet rs = stmt.getGeneratedKeys();
            if(rs.next()){
                Long id = rs.getLong(1);
                goods.setId(id);

                List<Long> photoIds = goods.getGoodsPhotos();
                if(photoIds!=null){
                    for (Long photoId : photoIds) {
                        GoodsPhoto goodsPhoto = new GoodsPhoto();
                        goodsPhoto.setGoodsId(id);
                        goodsPhoto.setPhotoId(photoId);
                        GoodsPhotoDao goodsPhotoDao = new GoodsPhotoDao();
                        goodsPhotoDao.save(goodsPhoto);
                    }
                }

            }
            return Constants.SUCCESS;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }


    public int update(Goods goods)  {
        try{
            String sql = "update dushibao_goods set name=?,price=?,stock=?,details=? where id=?";
            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setObject(1,goods.getName());
            stmt.setObject(2,goods.getPrice());
            stmt.setObject(3,goods.getStock());
            stmt.setObject(4,goods.getDetails());

            stmt.setLong(5,goods.getId());

            stmt.executeUpdate();
            return Constants.SUCCESS;
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
    public Page page(Page page , Map<String,Object> params) {

        try{
            StringBuilder querySql = new StringBuilder("select id,addTime,name,price,stock,details from dushibao_goods where 1=1 ");

            if(ObjectUtil.isNotEmpty(params.get("startTime"))){
                querySql.append(" and addTime > '"+params.get("startTime")+"'");
            }

            if(ObjectUtil.isNotEmpty(params.get("endTime"))){
                querySql.append(" and addTime < '"+params.get("endTime")+"'");
            }

            querySql.append(" limit ?,? ");

            Connection conn = DBUtils.getConnection();

            PreparedStatement stmt = conn.prepareStatement(querySql.toString());
            stmt.setInt(1,page.getStart());
            stmt.setInt(2,page.getPageSize());

            ResultSet rs = stmt.executeQuery();

            List<Object> objs = new ArrayList<>();
            while(rs.next()){
                Goods goods = new Goods();
                goods.setId(rs.getLong("id"));
                goods.setAddTime(rs.getTimestamp("addTime").toLocalDateTime());
                goods.setName(rs.getString("name"));
                goods.setPrice(rs.getBigDecimal("price"));
                goods.setStock(rs.getInt("stock"));
                goods.setDetails(rs.getString("details"));
                objs.add(goods);
            }

            DBUtils.close(rs,stmt,conn);

            StringBuilder countSql = new StringBuilder("select count(*) as total from dushibao_goods where 1=1 ");

            if(ObjectUtil.isNotEmpty(params.get("startTime"))){
                countSql.append(" and addTime > '"+params.get("startTime")+"'");
            }

            if(ObjectUtil.isNotEmpty(params.get("endTime"))){
                countSql.append(" and addTime < '"+params.get("endTime")+"'");
            }

            conn = DBUtils.getConnection();
            stmt = conn.prepareStatement(countSql.toString());

            rs = stmt.executeQuery();

            int total = 0;
            if(rs.next()){
                total = rs.getInt("total");
            }

            Page iPage = new Page();
            iPage.setCurrent(page.getCurrent());
            iPage.setPageSize(page.getPageSize());
            iPage.setData(objs);
            iPage.setPages(total % iPage.getPageSize()==0?total/ iPage.getPageSize():total/ iPage.getPageSize()+1);
            iPage.setTotal(total);

            return iPage;
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    public Goods getById(long id) {
        try{
            StringBuilder sql = new StringBuilder();
            sql.append("select g.id as gid,g.addTime,g.`name`,g.price,g.stock,g.details,a.id as aid,a.path from dushibao_goods g " );
            sql.append("left join dushibao_goods_photo gp on g.id = gp.goods_id ");
            sql.append("left join dushibao_accessory a on a.id = gp.photo_id ");
            sql.append("where g.id = ? ");

            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setLong(1,id);
            ResultSet rs = stmt.executeQuery();
            Goods goods = new Goods();
            List<Accessory> photos = new ArrayList<>();

            while (rs.next()){
                goods.setId(rs.getLong("gid"));
                goods.setAddTime(rs.getTimestamp("addTime").toLocalDateTime());
                goods.setName(rs.getString("name"));
                goods.setPrice(rs.getBigDecimal("price"));
                goods.setStock(rs.getInt("stock"));
                goods.setDetails(rs.getString("details"));

                Long aid = rs.getLong("aid");
                String path = rs.getString("path");
                if(aid!=null && path!=null){
                    Accessory accessory = new Accessory();
                    accessory.setId(aid);
                    accessory.setPath(path);
                    photos.add(accessory);
                }
            }

            goods.setPhotos(photos);

            return goods;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }


    public Integer deleteById(List<Long> ids) {
        try{

            StringBuilder sql = new StringBuilder(" delete from dushibao_goods where id in ( ");
            for (int i = 0; i < ids.size(); i++) {
                sql.append(ids.get(i));

                if(i<ids.size()-1){
                    sql.append(",");
                }

            }
            sql.append(")");

            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            return Constants.SUCCESS;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
}
